<%@ page import="bean.PollUser, java.sql.*, connection.*, java.util.*" %>
<jsp:useBean id="user" class="bean.PollUser" scope="request" />
<jsp:setProperty name="user" property="*"/>

<%
/*
CREATE TABLE ParticipantList(	
	 ListID		INT		NOT NULL
	,IsTemplate	INT
    ,Name		VARCHAR2(40)	
	,MasterUsername	VARCHAR2(40)
	,Username	VARCHAR2(40)
	,CONSTRAINT pk_plist PRIMARY KEY(ListID,Username)
	,CONSTRAINT fk_plma FOREIGN KEY(MasterUsername) REFERENCES PollUser(Username) ON DELETE CASCADE
	,CONSTRAINT fk_username_plist FOREIGN KEY(Username) REFERENCES PollUser(Username) ON DELETE CASCADE
);



CREATE TABLE Poll(
	 ID			INT
	,Version number	default 0
	,Name		VARCHAR2(40)
	,Description	VARCHAR2(256)
	,CreateTime	DATE
	,Latitude	VARCHAR2(40)
	,Longitude	VARCHAR2(40)
	,CreatorName	VARCHAR2(40)
	,CONSTRAINT pk_polls PRIMARY KEY(ID,Version)
	,CONSTRAINT fk_polls FOREIGN KEY(CreatorName) REFERENCES PollUser(Username) 
ON DELETE CASCADE
);


CREATE TABLE Poll_ParticipantList (
		 PollID		INT
		,PollVersion	INT
		,PartListID	INT
		,Username	VARCHAR2(40)
		,CONSTRAINT pk_poll_plist PRIMARY KEY(PollID,PollVersion,PartListID,Username)
		,CONSTRAINT fk_pollid_plists FOREIGN KEY(PollID,PollVersion) REFERENCES Poll(ID,Version) ON DELETE CASCADE
		,CONSTRAINT fk_plistid_plist FOREIGN KEY(PartListID,Username) REFERENCES ParticipantList(ListID,Username) ON DELETE CASCADE
	);
*/
Connection conn = new DbConnection().getConn();

String username = user.getUsername();
System.out.println(username);
String sql= "SELECT Poll.ID, Poll.Name, Poll.Description FROM Poll, Poll_ParticipantList, ParticipantList " +
	"WHERE ParticipantList.Username = '"+username+"' AND "+ 
	"Poll_ParticipantList.PartListID = ParticipantList.ListID AND "+
	"Poll.ID = Poll_ParticipantList.PollID";
	

System.out.println(sql);
PreparedStatement ps = conn.prepareStatement(sql);
ArrayList<String[]> polls = new ArrayList<String[]>();
boolean hasPolls = false;
try{
	ResultSet rs = ps.executeQuery();
	while(rs.next()){
		hasPolls = true;
		String temp[] = {"", "", "", ""};
		temp[0] = rs.getString("ID");
		temp[1] = rs.getString("NAME");
		temp[2] = rs.getString("DESCRIPTION");
		polls.add(temp);
		//temp[3] is reserved for status
	}
}catch(SQLException e){
	System.out.print(e.toString());
}

//now find the open question status of all polls
for(String[] s : polls){
	String pollid = s[0];
	s[3] = "0";
	sql = "SELECT DISTINCT Question.Status FROM Question, Poll, Poll_Question WHERE " +
	"Question.QuestionID = Poll_Question.QuestionID AND "+
	"Poll_Question.PollID = Poll.ID AND "+
	"Poll.ID = "+pollid;
	System.out.println(sql);
	PreparedStatement ps2 = conn.prepareStatement(sql);
	try{
		ResultSet rs2 = ps2.executeQuery();
		while(rs2.next()){
			String status = rs2.getString("STATUS");
			if(status == "1"){
				s[3] = status;
				break;
			}
		}
		System.out.println(s[3]);
	}catch(SQLException e){
		System.out.println(e.toString());
	}
}

%>
<div class="entrytitle">
Polls assigned to you
	<div class="entry">
		<%
		if(hasPolls){
			%>
			<table class="blockTable">
			<thead>
			
			<tr>
				<td><strong>Name</strong></td>
				<td><strong>Open</strong></td>
				<td><strong>Description</strong></td>
			</tr>
			</thead>
			<tbody>
			<%
			for(String[] curr : polls){
				%><tr><td><%
				out.write(curr[1]);
				%></td><td><%
				if(curr[3] == "1"){
					%><img src="/DBP/images/open.png"/><%
				}else{
					%><img src="/DBP/images/closed.png"/><%
				}
				%></td><td><%
				out.write(curr[2]);
				%></td></tr><%
			}
			%>
			</tbody>
			</table>
			<%
		}else{
			out.write("You have no polls assigned to you. <br/> How tragic :(");
		}
		%>
	</div>
</div>

